import pandas as pd
# from sklearn.preprocessing import MinMaxScaler
import numpy as np
import pymysql
import time
from datetime import datetime

import pymysql.cursors
from confi import CITY_DICT

class MysqlUtils(object):
    def __init__(self,*args):
        self.conn= pymysql.connect(
            host='localhost',
            user="root",
            passwd="MYSQL123",
            db="scenic",
            port=3306,
            charset="utf8"
        )
            
    def get_scenic_data(self):
        """"
        获取数据
        """
        cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = ''' 
        SELECT LEFT(u.id_no,4) as citv_code,DATE_FORMAT(o.create_time,'%Y-%m') as month, COUNT(u.id) as visitor_count
        FROM ticket_order o JOIN ticket_order_user_rel u on o.id = u.order_id = o.id 
        WHERE
        LENGTH(u.id_no) = 18 and o.pay_time is not null and o.pay_time is not null
        GROUP BY citv_code, DATE_FORMAT(o.create_time,'%Y-%m')
        
        '''
        cursor.execute(sql)
        ret = cursor.fetchall()
        print(ret)
        new_list = []
        for item in ret:
            if item['citv_code'] not in CITY_DICT:
                continue
            new_list.append({
                'citv_code': item['citv_code'],
                'month': item['month'],
                'visitor_count': item['visitor_count'],
                'city_name':CITY_DICT[item['citv_code']]
            })
        df_city_monthly = pd.DataFrame(new_list)
        df_city_monthly['month'] = pd.to_datetime(df_city_monthly['month'] + '-01')
        print(df_city_monthly.head)
    
        def calculate_baseline(df, current_month, window_size=6):
            #提取当月数据
            df_current = df[df['month'] == current_month]
            #提取历史数据
            history_start = current_month - pd.DateOffset(month=window_size)
            df_history = df[(df['month'] >= history_start) & (df['month'] < current_month)]
            
            #按城市计算和标准差
            df_baseline = df_history.groupby('city_name')['vistor_count'].agg(['mean', 'std']).reset_index()
            df_baseline.rename(columns = {'mean': 'hist_mean','std': 'hist_std'},inplace=True)
            
            #合并当前月数据
            df_merged = df_current.merge(df_baseline, on='city_name', how='left')
            return df_merged
        
        #假设当前月是2024-12
        current_month = pd.to_datetime('2024-12-01')
        df_merged = calculate_baseline(df_city_monthly,current_month)
        
        #计算z_score
        df_merged['z_score'] = (df_merged['visitor_count'] - df_merged['hist_mean']) / df_merged['hist_std']
        
        #标记暴增暴跌的城市(z_score > 3 or z_score < -3])
        df_increase = df_merged[df_merged['z_score'] > 3 ]
                                # or df_merged['z_score'] < -3]
        df_reduce = df_merged[df_merged['z_score'] < -3]
        print(df_increase)
        print('------------------')
        print(df_reduce)
    # print('------------------')
    # return df_merged
        
        
if  __name__=='__main__':
    mu = MysqlUtils()
    mu.get_scenic_data()